﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LTSMZ.life.Model;
using System.Data.SqlClient;
using System.Data;
using System.Collections;


namespace LTSMZ.life.DAL
{
    public  class MemberDal
    {
        Qianli.DirSir.Common.SQLHelper sql = new Qianli.DirSir.Common.SQLHelper();
        /// <summary>
        /// 显示会员信息
        /// </summary>
        /// <returns></returns>
        public List<Member> ShowMember()
        {
            string str = "select [Mid],[Mname],[Mpassword],[NickName],[RealName],[Sex],[Tel] from Member";
            DataTable table = sql.ExecDataTable(str);
            List<Member> li = new List<Member>();
            foreach (DataRow i in table.Rows)
            {
                Member m = new Member();
                m.Mid=Convert.ToInt32(i["Mid"]);
                m.Mname=i["Mname"].ToString();
                m.Mpassword=i["Mpassword"].ToString();
                m.NickName=i["NickName"].ToString();
                m.RealName=i["RealName"].ToString();
                m.Sex=i["Sex"].ToString();
                m.Tel = i["Tel"].ToString();
                li.Add(m);
            }
            return li;
        }
        /// <summary>
        /// 删除会员信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelMember(int id)
        {
            string str = "delete from Member where Mid="+id+"";
            return id=sql.ExecNonquery(str);
        }
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int DengMember(string name,string pwd)
        {
            string str = "select Mid from Member where Mname='" + name + "' and Mpassword='" + pwd + "'";
            int ss = Convert.ToInt32(sql.ExecScalar(str));
            return ss;
        }

        /// <summary>
        /// 注册
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int ZhuMember(Member m)
        {
            int i=0;
            string str = string.Format("insert into Member values('{0}','{1}','{2}','{3}','{4}','{5}')",m.Mname,m.Mpassword,m.NickName,m.RealName,m.Sex,m.Tel);
            return i = sql.ExecNonquery(str);
        }
        ///// <summary>
        ///// 根据账号获取编号
        ///// </summary>
        ///// <param name="name"></param>
        ///// <returns></returns>
        //public int GetIdByName(string name)
        //{
        //    int i=0;
        //    string str = "select mid from Member where Mname='"+name+"'";
        //    return i = Convert.ToInt32(sql.ExecScalar(str));
        //}
        /// <summary>
        /// 根据会员编号和商品编号判断该会员有没有买过该商品
        /// </summary>
        /// <param name="Mid"></param>
        /// <param name="Pid"></param>
        /// <returns></returns>
        public int GetCountByMidandPid(string name,int Pid)
        {
            string str = "select COUNT(*) from Member inner join Orders on Orders.Mid=Member.Mid join OrderSon on OrderSon.OrderID=Orders.OrderID join Product on Product.Pid=OrderSon.Pid where Member.Mname='"+name+"' and Product.Pid=" + Pid + "";
            return Convert.ToInt32(sql.ExecScalar(str));
        }
        /// <summary>
        /// 获取当前登录人的信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public Member GetByNameAll(string name)
        {
            string str = "select * from Member where Member.Mname='"+name+"'";
            SqlDataReader reader = sql.ExecReader(str);
            Member m = new Member();
            while (reader.Read())
            {
                m.Mid = Convert.ToInt32(reader["Mid"]);
                m.Mname = reader["Mname"].ToString();
                m.NickName = reader["NickName"].ToString();
                m.RealName = reader["RealName"].ToString();
                m.Tel = reader["Tel"].ToString();
            }
            return m;
        }
    }
}
